﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SuraQR.Bean;
using System.Diagnostics;

namespace SuraQR.Config
{
    class SQLUtils
    {
        private const string ADDR_TB = "F1Address";
        private static string addr_sql = "INSERT INTO " + ADDR_TB + " (row_id,name, factoryName, createDate,idCardNo,houseNo,moo,soi,street,tumbol,amphur,province,zipcode,telNo) "
                + "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )";


        public static string insertAddr(string row_id,F1Address f)
        {
          string s =  replateValue(addr_sql, new string[] { row_id, f.Name,f.FactoryName,f.CreateDate,f.IdCardNo,f.HouseNo,f.Moo,f.Soi,f.Street,f.Tumbol,f.Amphur
            ,f.Province,f.Zipcode,f.TelNo});

            return s;
        }


        private const string TAX_TB = "TAXSR120";
        private static string tax_sql = "INSERT INTO " + TAX_TB + " (row_id,taxReturn, taxPrepay, taxRoyalReturn,taxMinistryOfInterior,taxTypeRadio,data6_1,data6_2,data7,data8,data9,data10,data11,data12,data13,data14,data15) "
                + "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)";
        
        public static string insertTax(string row_id, Tax t)
        {
            string s = replateValue(tax_sql, new object[] { row_id, t.TaxReturn, t.TexPrepay, t.TaxRoyalReturn, t.TaxMinistryOfInterior, t.TaxTypeRadio, t.Data6_1, t.Data6_2, t.Data7, t.Data8, t.Data9, t.Data10, t.Data11, t.Data12, t.Data13, t.Data14, t.Data15 });

            return s;
        }

        private const string ORDER_TB = "ORDERS";
        private static string order_sql = "INSERT INTO " + ORDER_TB + " (row_id,orderNo,alcohorName,alcohorType,alcohorDegree,bottlePerLitter,amountBottle,taxPerLitter,wholesalePrice1,wholesalePrice2,adValoremTax,quantityTax,overLoadDegreeTax,amountQuantityTax,totalAdValoremTax,totalQuantityTax) "
                + "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?  )";

        public static string insertOrder(string row_id, Order o)
        {
            string s = replateValue(order_sql, new object[] { row_id, o.OrderNo,o.AlcohorName,o.AlcohorType,o.AlcohorDegree, o.BottlePerLitter,o.AmountBottle,o.TaxPerLitter,o.WholesalePrice1,o.WholesalePrice2,o.AdValoremTax,o.QuantityTax,o.OverLoadDegreeTax,o.AmountQuantityTax,o.TotalAdValoremTax,o.TotalQuantityTax});

            return s;
        }


        private static string replateValue(string sql, object[] values)
        {
            try
            {
                int i = 0;
                while (sql.IndexOf("?") != -1)
                {
                    if (values[i] is string)
                    {
                        sql = ReplaceFirst(sql, "?", "'" + (string)values[i] + "'");
                    }
                    else if (values[i] is double)
                    {
                        sql = ReplaceFirst(sql, "?", "" + (double)values[i]);
                    }
                    else if (values[i] == null)
                    {
                        sql = ReplaceFirst(sql, "?", "NULL");
                    }
                    else if (values[i] is int)
                    {
                        sql = ReplaceFirst(sql, "?", "" + (int)values[i]);
                    }
                    else
                    {
                        Debug.WriteLine("OTHER : " +  values[i] );
                    }

                    i++;
                }
            }catch (IndexOutOfRangeException e){
                Debug.WriteLine(e);
                throw new Exception("Params (?) And Value not equal");
            }
            Debug.WriteLine(sql);
            return sql;
        }

        private static string ReplaceFirst(string text, string search, string replace)
        {
            int pos = text.IndexOf(search);
            if (pos < 0)
            {
                return text;
            }
            return text.Substring(0, pos) + replace + text.Substring(pos + search.Length);
        }

    }
}
